
use std::str::FromStr;
use crate::{app_error::AppError, models::metainfo::SongMetaInfo};
use anyhow::Context;
use chrono::NaiveTime;
use serde_json::json;
use sqlx::PgPool;
use crate::config::AppConfig;


#[derive(Clone)]
pub struct SongsDao{
    pub pool: PgPool
}


impl SongsDao {

    pub async fn insert_song(&self, song: &SongMetaInfo) -> Result<(), AppError> {
        sqlx::query("INSERT INTO songs (title, artist, lyricist, composer, album, genre, lyrics, release_date, file_path) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)")
            .bind(&song.title)
            .bind(&song.artist)
            .bind(&song.lyricist)
            .bind(&song.composer)
            .bind(&song.album)
            .bind(&song.genre)
            .bind(&song.lyrics)
            .bind(&song.release_date)
            .bind(&song.file_path)
            .execute(&self.pool)
            .await?;
        Ok(())
    }

    // 分页查询歌曲
    pub async fn get_songs(&self, page: i64, page_size: i64) -> Result<Vec<SongMetaInfo>, AppError> {
        let mut songs = Vec::new();
        let mut stmt = sqlx::query_as::<_, SongMetaInfo>("SELECT * FROM songs ORDER BY song_id ASC LIMIT $1 OFFSET $2")
            .bind(page_size)
            .bind((page - 1) * page_size)
            .fetch_all(&self.pool)
            .await?;
        for song in stmt.drain(..) {
            songs.push(song);
        }
        Ok(songs)
    }

    pub async fn get_song_count(&self) -> Result<i64, AppError> {
        let count = sqlx::query_scalar("SELECT COUNT(*) FROM songs")
            .fetch_one(&self.pool)
            .await?;
        Ok(count)
    }

    pub async fn get_song(&self, id: i64) -> Result<SongMetaInfo, AppError> {
        let song = sqlx::query_as::<_, SongMetaInfo>("SELECT * FROM songs WHERE song_id = $1")
            .bind(id)
            .fetch_one(&self.pool)
            .await?;
        Ok(song)
    }
    
    pub async fn delete_song(&self, id: i64) -> Result<(), AppError> {
        sqlx::query("DELETE FROM songs WHERE song_id = $1")
            .bind(id)
            .execute(&self.pool)
            .await?;
        Ok(())
    }

    // 获取days天内最新发布的limit首歌曲
    pub async fn get_latest_songs(&self, limit: i64, days: i64) -> Result<Vec<SongMetaInfo>, AppError> {
        // 创建一个SQL查询语句，查询songs表，按照created_at字段降序排列，限制查询结果数量为count
        let songs:Vec<SongMetaInfo> = sqlx::query_as::<_, SongMetaInfo>(
            r#"SELECT *
                    FROM songs
                    WHERE created_at >= NOW() - INTERVAL '$1 days'
                    ORDER BY created_at DESC
                    LIMIT $2;"#)
            .bind(days)
            .bind(limit)
            .fetch_all(&self.pool)
            .await?;
        
        Ok(songs)
    }

    pub async fn get_songs_by_title(&self, title: &str) -> Result<Vec<SongMetaInfo>, AppError> {
        let songs = sqlx::query_as::<_, SongMetaInfo>("SELECT * FROM songs WHERE title = $1")
            .bind(title)
            .fetch_all(&self.pool)
            .await?;
        Ok(songs)
    }

    pub async fn get_songs_by_artist(&self, artist: &str) -> Result<Vec<SongMetaInfo>, AppError> {
        let songs = sqlx::query_as::<_, SongMetaInfo>("SELECT * FROM songs WHERE artist = $1")
            .bind(artist)
            .fetch_all(&self.pool)
            .await?;
        Ok(songs)
    }

    pub async fn search_songs_by_title(&self, title: &str) -> Result<Vec<SongMetaInfo>, AppError> {
        let songs = sqlx::query_as::<_, SongMetaInfo>("SELECT * FROM songs WHERE title LIKE $1")
            .bind(format!("%{}%", title))
            .fetch_all(&self.pool)
            .await?;
        Ok(songs)
    }

    // 更新歌曲信息
}
